using System;
using System.Data;
using System.Data.SqlClient;
using Framework.Library;
using Framework.Library.Utilities;
using Framework.Module.Adsvertising.Lib.Data;

namespace Framework.Module.Adsvertising.Lib.DataAccess
{
	public class AdvertiseDB
	{
		public static DataTable GetByPositionID (int _positionID, bool _isEnable, string _paramCollections) 
		{
			string enable = _isEnable? "1":"0";
			string strSelectByOnlyID = " SELECT * FROM Adv_Advertises WHERE Advertise_PositionID = " + _positionID  +
				" AND Advertise_Enable = " + enable + " AND PATINDEX('%|' + CAST({0} as varchar) + '|%','|' + Advertise_Params ) >0 "
				+ " AND Advertise_Lang='" + AppEnv.GetLanguage() + "'";
			string[] arrParams = _paramCollections.Split(new char[] {'|'});
			string strCommand = string.Empty;
			foreach(string attachID in arrParams)
			{
				int id = ConvertUtility.ToInt32(attachID,-1);
				if (id != -1) strCommand += string.Format(strSelectByOnlyID,id) + " UNION";
			}
			if (strCommand.Length > 0)
			{
				strCommand = strCommand.Substring(0,strCommand.Length-6) + " ORDER BY Advertise_Priority ASC";
			}
			DataTable retVal = null;
			SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
 			
			SqlCommand dbCmd = new SqlCommand(strCommand, dbConn);
			dbCmd.CommandType = CommandType.Text;
			try
			{
				retVal = new DataTable() ;
				SqlDataAdapter da = new SqlDataAdapter(dbCmd);
				da.Fill(retVal);
			}
			finally
			{
				dbConn.Close();
			}
			return retVal; 
		}
		public static DataTable GetAvailables(int _positionID, int _paramID) 
		{
			DataTable retVal = null;
			SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
 			
			SqlCommand dbCmd = new SqlCommand("Adv_Advertises_GetAvailables", dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;
			dbCmd.Parameters.AddWithValue("@PositionID", _positionID);
			dbCmd.Parameters.AddWithValue("@AttachID", _paramID);
			dbCmd.Parameters.AddWithValue("@Advertise_Lang", AppEnv.GetLanguage());
			try
			{
				retVal = new DataTable() ;
				SqlDataAdapter da = new SqlDataAdapter(dbCmd);
				da.Fill(retVal);
			}
			finally
			{
				dbConn.Close();
			}
			return retVal; 
		}
		public static void SetPriority(int _advertise_ID, int _newPriority)
		{
			SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
			SqlCommand dbCmd = new SqlCommand("Adv_Advertises_SetPriority", dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;
			dbCmd.Parameters.AddWithValue("@Advertise_ID",_advertise_ID);
			dbCmd.Parameters.AddWithValue("@Advertise_Priority",_newPriority);
			try 
			{
				dbConn.Open();
				dbCmd.ExecuteNonQuery();
			}
			finally 
			{
				dbConn.Close();
			}
		}
		public static void Clicks(int _advertise_ID, int _clickCount)
		{
			SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
			SqlCommand dbCmd = new SqlCommand("Adv_Advertises_Clicks", dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;
			dbCmd.Parameters.AddWithValue("@Advertise_ID",_advertise_ID);
			dbCmd.Parameters.AddWithValue("@ClickCount",_clickCount);
			try 
			{
				dbConn.Open();
				dbCmd.ExecuteNonQuery();
			}
			finally 
			{
				dbConn.Close();
			}
		}
 
		public static void Delete (int _advertise_ID) 
		{
			SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
			SqlCommand dbCmd = new SqlCommand("Adv_Advertises_Delete", dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;
			dbCmd.Parameters.AddWithValue("@Advertise_ID",_advertise_ID);
			try 
			{
				dbConn.Open();
				dbCmd.ExecuteNonQuery();
			}
			finally 
			{
				dbConn.Close();
			}
		}
 
		public static int Insert (AdvertiseInfo _advertiseInfo) 
		{
			SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
			SqlCommand dbCmd = new SqlCommand("Adv_Advertises_Insert", dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;
			dbCmd.Parameters.AddWithValue("@Advertise_Name",_advertiseInfo.Advertise_Name);
			dbCmd.Parameters.AddWithValue("@Advertise_Path",_advertiseInfo.Advertise_Path);
			dbCmd.Parameters.AddWithValue("@Advertise_Width",_advertiseInfo.Advertise_Width);
			dbCmd.Parameters.AddWithValue("@Advertise_Height",_advertiseInfo.Advertise_Height);
			dbCmd.Parameters.AddWithValue("@Advertise_StartDate",_advertiseInfo.Advertise_StartDate);
			dbCmd.Parameters.AddWithValue("@Advertise_EndDate",_advertiseInfo.Advertise_EndDate);			
			dbCmd.Parameters.AddWithValue("@Advertise_Type",_advertiseInfo.Advertise_Type);			
			dbCmd.Parameters.AddWithValue("@Advertise_RedirectURL",_advertiseInfo.Advertise_RedirectURL);			
			dbCmd.Parameters.AddWithValue("@Advertise_PositionID",_advertiseInfo.Advertise_PositionID);
			dbCmd.Parameters.AddWithValue("@Advertise_Enable",_advertiseInfo.Advertise_Enable);
			dbCmd.Parameters.AddWithValue("@Advertise_Params",_advertiseInfo.Advertise_Params);
			dbCmd.Parameters.AddWithValue("@Advertise_Lang",_advertiseInfo.Advertise_Lang);
			dbCmd.Parameters.AddWithValue("@Advertise_Target", _advertiseInfo.Advertise_Target);
			dbCmd.Parameters.AddWithValue("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
			try 
			{
				dbConn.Open();
				dbCmd.ExecuteNonQuery();
				return (int)dbCmd.Parameters["@RETURN_VALUE"].Value;
			}
			finally 
			{
				dbConn.Close();
			}
		}
 
		public static void Update (AdvertiseInfo _advertiseInfo) 
		{
			SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
			SqlCommand dbCmd = new SqlCommand("Adv_Advertises_Update", dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;
			dbCmd.Parameters.AddWithValue("@Advertise_ID",_advertiseInfo.Advertise_ID);
			dbCmd.Parameters.AddWithValue("@Advertise_Name",_advertiseInfo.Advertise_Name);
			dbCmd.Parameters.AddWithValue("@Advertise_Path",_advertiseInfo.Advertise_Path);
			dbCmd.Parameters.AddWithValue("@Advertise_Width",_advertiseInfo.Advertise_Width);
			dbCmd.Parameters.AddWithValue("@Advertise_Height",_advertiseInfo.Advertise_Height);
			dbCmd.Parameters.AddWithValue("@Advertise_StartDate",_advertiseInfo.Advertise_StartDate);			
			dbCmd.Parameters.AddWithValue("@Advertise_EndDate",_advertiseInfo.Advertise_EndDate);			
			dbCmd.Parameters.AddWithValue("@Advertise_Priority",_advertiseInfo.Advertise_Priority);
			dbCmd.Parameters.AddWithValue("@Advertise_Type",_advertiseInfo.Advertise_Type);
			dbCmd.Parameters.AddWithValue("@Advertise_Click",_advertiseInfo.Advertise_Click);
			dbCmd.Parameters.AddWithValue("@Advertise_RedirectURL",_advertiseInfo.Advertise_RedirectURL);			
			dbCmd.Parameters.AddWithValue("@Advertise_PositionID",_advertiseInfo.Advertise_PositionID);
			dbCmd.Parameters.AddWithValue("@Advertise_Enable",_advertiseInfo.Advertise_Enable);
			dbCmd.Parameters.AddWithValue("@Advertise_Params",_advertiseInfo.Advertise_Params);
			dbCmd.Parameters.AddWithValue("@Advertise_Lang",_advertiseInfo.Advertise_Lang);
			dbCmd.Parameters.AddWithValue("@Advertise_Target", _advertiseInfo.Advertise_Target);
			try 
			{
				dbConn.Open();
				dbCmd.ExecuteNonQuery();
			}
			finally 
			{
				dbConn.Close();
			}
		}
 
		public static AdvertiseInfo GetInfo ( int _advertise_ID ) 
		{
			AdvertiseInfo retVal = null;
			SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
			SqlCommand dbCmd = new SqlCommand("Adv_Advertises_GetInfo", dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;
			dbCmd.Parameters.AddWithValue("@Advertise_ID", _advertise_ID);
			try 
			{
				dbConn.Open();
				SqlDataReader dr = dbCmd.ExecuteReader();
				if (dr.Read()) 
				{
					retVal = new AdvertiseInfo();
					retVal.Advertise_ID=Convert.ToInt32(dr["Advertise_ID"]);
					retVal.Advertise_Name=Convert.ToString(dr["Advertise_Name"]);
					retVal.Advertise_Path=Convert.ToString(dr["Advertise_Path"]);
					retVal.Advertise_Width=Convert.ToInt32(dr["Advertise_Width"]);
					retVal.Advertise_Height=Convert.ToInt32(dr["Advertise_Height"]);
					retVal.Advertise_StartDate=Convert.ToDateTime(dr["Advertise_StartDate"]);
					retVal.Advertise_EndDate=Convert.ToDateTime(dr["Advertise_EndDate"]);					
					retVal.Advertise_Type=Convert.ToString(dr["Advertise_Type"]);
					retVal.Advertise_Priority=Convert.ToInt32(dr["Advertise_Priority"]);
					retVal.Advertise_Click=Convert.ToInt32(dr["Advertise_Click"]);
					retVal.Advertise_PositionID = Convert.ToInt32(dr["Advertise_PositionID"]);
					retVal.Advertise_RedirectURL=Convert.ToString(dr["Advertise_RedirectURL"]);					
					retVal.Advertise_Enable=Convert.ToBoolean(dr["Advertise_Enable"]);
					retVal.Advertise_Params = Convert.ToString(dr["Advertise_Params"]);
					retVal.Advertise_Lang = Convert.ToString(dr["Advertise_Lang"]);
					retVal.Advertise_Target = Convert.ToString(dr["Advertise_Target"]);
				}
				dr.Close();
			}
			finally
			{				
				dbConn.Close();
			}
			return retVal;
		}
 
	}
}

